using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Bet_Yatziv
{
    class DataAccessLayer
    {

        //********costumers********
        public static DataTable GetAllCostumers()
        {
            try
            {
               string query="Select * from tbl_costumers";

               DataTable dt = AdoHelper.ExecuteDataTable(query);

               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewCost(int ID, string firstName, string lastName, string address, string homePhone, string cellPhone, string payingMethod)
        {

            string query = "Insert into tbl_costumers values(" + ID + ", '" + firstName + "' , '" + lastName + "', '" + address + "', '" + homePhone + "', '" + cellPhone + "', '" + payingMethod + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateCost(int ID, string firstName, string lastName, string address, string homePhone, string cellPhone, string payingMethod)
        {
            string query = "Update tbl_costumers set costumerID=" + ID + ", firstName='" + firstName + "', lastName='" + lastName + "', address='" + address + "', homePhone='" + homePhone + "', cellPhone='" + cellPhone + "', payingMethod='" + payingMethod + "' where costumerID=" + ID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void DeleteCost(int ID)
        {
            string query = "Delete from tbl_costumers where costumerID=" + ID;
            AdoHelper.ExecuteNonQuery(query);
        }

        //********employees********
    public static DataTable GetAllEmployees()
        {
            try
            {
               string query="Select * from tbl_employees";

               DataTable dt = AdoHelper.ExecuteDataTable(query);

               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewEmp(int ID, string firstName, string lastName, string address, string cellPhone, int wage, DateTime bdate, int score)
        {
            string query = "Insert into tbl_employees values(" + ID + ", '" + firstName + "' , '" + lastName + "', '" + address + "', '" + cellPhone + "', " + wage + ", '" + bdate + "', " + score + ")";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateEmp(int ID, string firstName, string lastName, string address, string cellPhone, int wage, DateTime bdate)
        {
            string query = "Update tbl_employees set employeeID=" + ID + ", firstName='" + firstName + "', lastName='" + lastName + "', address='" + address + "', cellPhone='" + cellPhone + "', wage=" + wage + ", birthDate='" + bdate + "' where employeeID=" + ID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void DeleteEmp(int ID)
        {
            string query = "Delete from tbl_employees where employeeID=" + ID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void updateScore(int ID, int type)
        {       
            string query = "Update tbl_employees set score=" + type + " where employeeID=" + ID;
            AdoHelper.ExecuteNonQuery(query);
        }
        
    
    //********Rooms********
    public static DataTable GetAllRooms()
        {
            try
            {
               string query="Select * from tbl_rooms";

               DataTable dt = AdoHelper.ExecuteDataTable(query);

               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewRoom(int roomID, string type, int price, int maxBed, Boolean available)
        {
            string query = "Insert into tbl_rooms values(" + roomID + ", '" + type + "' , " + price + ", " + maxBed + ", " + available + ")";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateRoom(int roomID, string type, int price, int maxBed, Boolean available)
        {
            string query = "Update tbl_rooms set roomID=" + roomID + ", type='" + type + "', price=" + price + ", maxBeds=" + maxBed + ", available=" + available;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void DeleteRoom(int roomID)
        {
            string query = "Delete from tbl_rooms where roomID=" + roomID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateStat(int roomID, Boolean available)
         {
             string query = "Update tbl_rooms set available=" + available + " where roomID=" + roomID;
           AdoHelper.ExecuteNonQuery(query);
        }
 
    

     //********rentals********
    public static DataTable GetAllOrders()
        {
            try
            {
                string query = "Select * from tbl_orders";

               DataTable dt = AdoHelper.ExecuteDataTable(query);

               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewRent(int orderID, int customerID, int roomNum, int numberOfDays, DateTime rentDate)
        {
            string query = "Insert into tbl_orders values(" + orderID + " , " + customerID + " , " + roomNum + ", '" + rentDate + "', " + numberOfDays + ")";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateRent(int orderID, int customerID, int roomNum, int numberOfDays, DateTime rentDate)
        {
            string query = "Update tbl_orders set orderID=" + orderID + ", customerID=" + customerID + ", roomNum=" + roomNum + ", numberOfDays=" + numberOfDays + ", rentDate=" + rentDate + " where orderID=" + orderID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void DeleteOrder(int orderID)
        {
            string query = "Delete from tbl_orders where orderID=" + orderID;
            AdoHelper.ExecuteNonQuery(query);
        }

    
    


    //********warehouse********
    public static DataTable GetAllWarehouse()
        {
            try
            {
               string query="Select * from tbl_warehouse";

               DataTable dt = AdoHelper.ExecuteDataTable(query);

               return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewItem(int itemID, string name, int amount)
        {
            string query = "Insert into tbl_warehouse values(" + itemID + " , '" + name + "' , " + amount + ")";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void UpdateItem(int itemID, string name, int amount)
        {
            string query = "Update from tbl_warehouse set itemID=" + itemID + ", description=" + name + ", amount=" + amount + " where itemID=" + itemID;
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void DeleteItem(int itemID)
        {
            string query = "Delete from tbl_warehouse where itemID=" + itemID;
            AdoHelper.ExecuteNonQuery(query);
        }
    }

}
